﻿// --------------------------------------------------------------------------------------
// <copyright file="RetrieveContentDAO.cs" company="NGroup">
// Copyright (c) NGroup. All rights reserved.
// </copyright>
// --------------------------------------------------------------------------------------
// <history>
//     <change who="Bui Thanh Nam" date="2013.12.15">Create</change>
// </history>
// --------------------------------------------------------------------------------------

namespace NGroup.VTL.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using NGroup.VTL.Core.Const;
    using NGroup.VTL.Core.Utilities;
    using NGroup.VTL.DAL.Abstraction;
    using NGroup.VTL.DAL.Extension;
    using NGroup.VTL.Models;

    public class RetrieveContentDAO : IRetrieveContentDAO
    {
        public List<ProductModel> GetProductListByTypeAndPagingInfo(int parentTypeID, int? childTypeID, LanguageCodes lang, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetProductList";
            DataSet resultDS = null;
            object totalRows;
            List<ProductModel> productList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("ParentTypeID", parentTypeID);
                command.AddInParameter("ChildTypeID", childTypeID);
                command.AddInParameter("Lang", Convert.ToInt32(lang));

                command.AddOutParameter("TotalRows", 0);

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                productList = this.ConvertDataTableToProductListModel(resultTable);
            }

            return productList;
        }

        public Dictionary<string, List<ProductModel>> GetHomePageProductList(LanguageCodes lang)
        {
            string spName = "GetHomePageProductList";
            DataSet resultDS = null;

            List<ProductModel> sanphammoi = null;
            List<ProductModel> daydeothemoi = null;
            List<ProductModel> thenhuamoi = null;
            List<ProductModel> tintucmoi = null;
            Dictionary<string, List<ProductModel>> data = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.AddInParameter("Lang", Convert.ToInt32(lang));

                command.CommandType = CommandType.StoredProcedure;

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 2)
            {
                DataTable tabSanPhamMoi = resultDS.Tables[0];
                DataTable tabDayDeoTheMoi = resultDS.Tables[1];
                DataTable tabTheNhuaMoi = resultDS.Tables[2];
                DataTable tabTinTucMoi = resultDS.Tables[3];

                sanphammoi = this.ConvertDataTableToHomePageProductList(tabSanPhamMoi);
                daydeothemoi = this.ConvertDataTableToHomePageProductList(tabDayDeoTheMoi);
                thenhuamoi = this.ConvertDataTableToHomePageProductList(tabTheNhuaMoi);
                tintucmoi = this.ConvertDataTableToHomePageProductList(tabTinTucMoi);
                data = new Dictionary<string, List<ProductModel>>();

                data.Add(GeneralConfiguration.TrangChu_DayDeoMoi, daydeothemoi);
                data.Add(GeneralConfiguration.TrangChu_SanPhamMoi, sanphammoi);
                data.Add(GeneralConfiguration.TrangChu_TheNhuaMoi, thenhuamoi);
                data.Add(GeneralConfiguration.TrangChu_TinTucMoi, tintucmoi);
            }

            return data;
        }

        public bool PlaceOrder(OrderModel order)
        {
            string spName = "AddNewOrder";
            object newIdentity = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("ProductID", order.ProductID);
                command.AddInParameter("CustomerName", order.CustomerName);
                command.AddInParameter("CustomerPhone", order.CustomerPhone);
                command.AddInParameter("ShippingAddress", order.ShippingAddress);
                command.AddInParameter("OrderDate", order.OrderDate);
                command.AddInParameter("Amount", order.Amount);
                command.AddInParameter("ProductSupTypeID", order.ProductSupTypeID);
                command.AddInParameter("ProductTypeID", order.ProductTypeID);

                command.AddOutParameter("NewIdentity", -1);
                command.ExecuteNonQuery();

                newIdentity = command.GetParameterValue("NewIdentity");
            }

            long newIdentityValue;

            if (long.TryParse(newIdentity.ToString(), out newIdentityValue) && newIdentityValue != -1)
            {
                return true;
            }

            return false;
        }        

        public ProductModel GetProductDetail(int parentTypeID, int childTypeID, string uniqueString, LanguageCodes lang, out List<ProductModel> relatedProducts)
        {
            string spName = "GetProductDetail";
            DataSet resultDS = null;
            var product = new ProductModel();
            relatedProducts = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("UniqueString", uniqueString);
                command.AddInParameter("ParentTypeID", parentTypeID);
                command.AddInParameter("ChildTypeID", childTypeID);
                command.AddInParameter("Lang", Convert.ToInt32(lang));

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0 && resultDS.Tables[0].Rows.Count > 0)
            {
                DataRow result = resultDS.Tables[0].Rows[0];
                product = ConvertDataTableToProductModel(result);
                if (resultDS.Tables.Count > 1)
                {
                    DataTable tblRelatedProduct = resultDS.Tables[1];

                    if (tblRelatedProduct != null && tblRelatedProduct.Rows.Count > 0)
                    {
                        // TODO: parse tblRelatedProduct to relatedProducts object.
                        relatedProducts = ConvertDataTableToProductListModel(tblRelatedProduct);
                    }
                }
            }

            return product;
        }

        public NewsModel GetNewsDetail(string uniqueString, LanguageCodes lang)
        {
            string spName = "GetNewsDetail";
            DataSet resultDS = null;
            var news = new NewsModel();
            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("UniqueString", uniqueString);
                command.AddInParameter("Lang", Convert.ToInt32(lang));

                resultDS = command.ExecuteDataset();
            }

            if (resultDS != null && resultDS.Tables.Count > 0 && resultDS.Tables[0].Rows.Count > 0)
            {
                DataRow result = resultDS.Tables[0].Rows[0];
                news = ConvertDataTableToNewsModel(result);
            }

            return news;
        }

        public List<NewsModel> GetNewsListByTypeAndPagingInfo(int? typeID, LanguageCodes lang, ref PagingInfoModel pagingInfo)
        {
            string spName = "GetNewsList";
            string typeList = typeID.GetHashCode().ToString();

            DataSet resultDS = null;
            object totalRows;
            List<NewsModel> newsList = null;

            using (var conn = BaseDAO.GetConnectionString())
            {
                conn.Open();

                SqlCommand command = new SqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;

                command.AddInParameter("PageIndex", pagingInfo.CurrentPage);
                command.AddInParameter("PageSize", pagingInfo.PageSize);
                command.AddInParameter("TypeID", typeList);
                command.AddOutParameter("TotalRows", 0);
                command.AddInParameter("Lang", Convert.ToInt32(lang));

                resultDS = command.ExecuteDataset();
                totalRows = command.GetParameterValue("TotalRows");
            }

            if (resultDS != null && resultDS.Tables.Count > 0)
            {
                int totalRowsValue;

                if (int.TryParse(totalRows.ToString(), out totalRowsValue))
                {
                    pagingInfo.UpdateValues(totalRowsValue);
                }

                DataTable resultTable = resultDS.Tables[0];

                newsList = this.ConvertDataTableToNewsListModel(resultTable);
            }

            return newsList;
        }

        private List<ProductModel> ConvertDataTableToProductListModel(DataTable data)
        {
            var productList = new List<ProductModel>();

            foreach (DataRow dr in data.Rows)
            {
                var product = this.ConvertDataTableToProductModel(dr);
                productList.Add(product);
            }

            return productList;
        }

        private List<NewsModel> ConvertDataTableToNewsListModel(DataTable data)
        {
            var newsList = new List<NewsModel>();

            foreach (DataRow dr in data.Rows)
            {
                var news = ConvertDataTableToNewsModel(dr);
                newsList.Add(news);
            }

            return newsList;
        }

        private List<ProductModel> ConvertDataTableToHomePageProductList(DataTable data)
        {
            var productList = new List<ProductModel>();

            foreach (DataRow dr in data.Rows)
            {
                var product = this.ConvertDataToHomePageProductModel(dr);
                productList.Add(product);
            }

            return productList;
        }

        private ProductModel ConvertDataTableToProductModel(DataRow dr)
        {
            var product = new ProductModel();

            product.ProductID = SqlUtil<int>.GetValue(dr, "ID");
            product.HomePageDisplayBlockID = SqlUtil<int>.GetValue(dr, "HomePageDisplayBlockID");
            product.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
            product.SupTypeID = SqlUtil<int>.GetValue(dr, "SupTypeID");
            product.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
            product.Title = SqlUtil<string>.GetValue(dr, "Title");
            product.MetaContent = SqlUtil<string>.GetValue(dr, "MetaContent");
            product.Content = SqlUtil<string>.GetValue(dr, "Content");
            product.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
            product.ProductDetail = SqlUtil<string>.GetValue(dr, "ProductDetail");
            product.DeliveryDetail = SqlUtil<string>.GetValue(dr, "DeliveryDetail");
            product.ImageSmallURL = SqlUtil<string>.GetValue(dr, "ImageSmallURL");
            product.ImageLargeURL = SqlUtil<string>.GetValue(dr, "ImageLargeURL");
            product.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
            product.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
            product.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
            product.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
            product.Price = SqlUtil<string>.GetValue(dr, "Price");
            product.MetaDescription = SqlUtil<string>.GetValue(dr, "MetaDescription");

            return product;
        }

        private NewsModel ConvertDataTableToNewsModel(DataRow dr)
        {
            var news = new NewsModel();

            news.NewsID = SqlUtil<int>.GetValue(dr, "ID");
            news.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
            news.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
            news.Title = SqlUtil<string>.GetValue(dr, "Title");
            news.IsActive = SqlUtil<bool>.GetValue(dr, "IsActive");
            news.BriefContent = SqlUtil<string>.GetValue(dr, "BriefContent");
            news.FullContent = SqlUtil<string>.GetValue(dr, "FullContent");
            news.ImageSmallURL = SqlUtil<string>.GetValue(dr, "ImageSmallURL");
            news.CreatedAt = SqlUtil<DateTime>.GetValue(dr, "CreatedAt");
            news.CreatedBy = SqlUtil<string>.GetValue(dr, "CreatedBy");
            news.LastUpdatedAt = SqlUtil<DateTime>.GetValue(dr, "LastUpdatedAt");
            news.LastUpdatedBy = SqlUtil<string>.GetValue(dr, "LastUpdatedBy");
            news.MetaContent = SqlUtil<string>.GetValue(dr, "MetaContent");
            news.MetaDescription = SqlUtil<string>.GetValue(dr, "MetaDescription");

            return news;
        }

        private ProductModel ConvertDataToHomePageProductModel(DataRow dr)
        {
            var product = new ProductModel();

            product.ProductID = SqlUtil<int>.GetValue(dr, "ID");
            product.TypeID = SqlUtil<int>.GetValue(dr, "TypeID");
            product.SupTypeID = SqlUtil<int>.GetValue(dr, "SupTypeID");
            product.UniqueString = SqlUtil<string>.GetValue(dr, "UniqueString");
            product.Title = SqlUtil<string>.GetValue(dr, "Title");
            product.ImageSmallURL = SqlUtil<string>.GetValue(dr, "ImageSmallURL");
            product.ImageLargeURL = SqlUtil<string>.GetValue(dr, "ImageLargeURL");
            product.Content = SqlUtil<string>.GetValue(dr, "Content");

            return product;
        }
    }
}
